// Decompiled by Jad v1.5.8g. Copyright 2001 Pavel Kouznetsov.
// Jad home page: http://www.kpdus.com/jad.html
// Decompiler options: packimports(3) 
// Source File Name:   Tyg_twQuery1.java

package MaintMenu.Shipinfo1;

import javax.swing.*;
import java.sql.*;

public class Tyg_twQuery1
{

    public  Tyg_twQuery1(String poid, String isCapa, String boName)
    {
        try
        {
            Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
            String dataSourceName = "dbLI";
            String dbURL = (new StringBuilder()).append("jdbc:odbc:").append(dataSourceName).toString();
            Connection con = DriverManager.getConnection(dbURL, "", "");
            Statement st = con.createStatement();
            Statement st2 = con.createStatement();
            Statement st4 = con.createStatement();
            Statement st5 = con.createStatement();
            if(isCapa.equals(""))
                isCapa = "C";
            try
            {
                if(!poid.equals(""))
                {
                    st.execute((new StringBuilder()).append("update tblcontainershipped a inner join tblpurchaseorders b on a.containername=b.[ref#] set a.capa = null where a.capa='null' and b.poid = ").append(poid).append(";").toString());
                    st.execute((new StringBuilder()).append("select [ref#] as ref from tblpurchaseorders where poid = ").append(poid).append(";").toString());
                    ResultSet rs = st.getResultSet();
                    String container = null;
                    if(rs != null)
                    {
                        rs.next();
                        container = rs.getString("ref");
                    }
                    st2.execute((new StringBuilder()).append("select * from tblpurchaseorderdetail where poid = ").append(poid).append(";").toString());
                    ResultSet rs2 = st2.getResultSet();
                    String duedate = null;
                    String duedate1 = null;
                    if(rs2 != null)
                    {
                        rs2.next();
                        duedate = rs2.getString("duedate");
                        duedate1 = (new StringBuilder()).append(duedate.substring(5, 7)).append("/").append(duedate.substring(8, 10)).append("/").append(duedate.substring(2, 4)).toString();
                    }
                    st4.execute((new StringBuilder()).append("insert into tblcontainerheader(container,arrival) values ('").append(container).append("',#").append(duedate1).append("#);").toString());
                    st5.execute((new StringBuilder()).append("select * from tblcontainerheader where container = '").append(container).append("';").toString());
                    ResultSet rs5 = st5.getResultSet();
                    long containerKey = 0L;
                    if(rs5 != null)
                    {
                        rs5.next();
                        containerKey = rs5.getLong("containerkey");
                    }
                    st4.execute((new StringBuilder()).append("INSERT INTO TBLCONTAINERDETAIL ( CONTAINERKEY, INVID, PN, QTY, RECEIVED ) SELECT ").append(containerKey).append(", INVID, [PART#], quantity, 0 ").append("FROM tblpurchaseorderdetail ").append("where poid = ").append(poid).append(";").toString());
                    st4.execute((new StringBuilder()).append("UPDATE TBLBCN AS A INNER JOIN tblcontainershipped AS B ON A.[PART#]=B.dnk_no SET A.CAPA = IIF(B.CAPA='").append(isCapa).append("',-1,0), ").append("A.TYG_TW_K = B.pack, A.TYG_TW_C = B.CUFT/B.QTY, ").append("A.TYG_TW_P = B.PRICE where b.containername='").append(container).append("';").toString());
                    st4.execute((new StringBuilder()).append("UPDATE TBLCLN AS A INNER JOIN tblcontainershipped AS B ON A.[PART#]=B.dnk_no SET A.CAPA = IIF(B.CAPA='").append(isCapa).append("',-1,0), ").append("A.TYG_TW_K = B.pack, A.TYG_TW_C = B.CUFT/B.QTY, ").append("A.TYG_TW_P = B.PRICE where b.containername='").append(container).append("';").toString());
                    st4.execute((new StringBuilder()).append("UPDATE TBLHFN AS A INNER JOIN tblcontainershipped AS B ON A.[PART#]=B.dnk_no SET A.CAPA = IIF(B.CAPA='").append(isCapa).append("',-1,0), ").append("A.TYG_TW_K = B.pack, A.TYG_TW_C = B.CUFT/B.QTY, ").append("A.TYG_TW_P = B.PRICE where b.containername='").append(container).append("';").toString());
                    st4.execute((new StringBuilder()).append("UPDATE TBLMRN AS A INNER JOIN tblcontainershipped AS B ON A.[PART#]=B.dnk_no SET A.CAPA = IIF(B.CAPA='").append(isCapa).append("',-1,0), ").append("A.TYG_TW_K = B.pack, A.TYG_TW_C = B.CUFT/B.QTY, ").append("A.TYG_TW_P = B.PRICE where b.containername='").append(container).append("';").toString());
                    st4.execute((new StringBuilder()).append("UPDATE TBLOTN AS A INNER JOIN tblcontainershipped AS B ON A.[PART#]=B.dnk_no SET A.CAPA = IIF(B.CAPA='").append(isCapa).append("',-1,0), ").append("A.TYG_TW_K = B.pack, A.TYG_TW_C = B.CUFT/B.QTY, ").append("A.TYG_TW_P = B.PRICE where b.containername='").append(container).append("';").toString());
                    st4.execute("delete * from tbltemppo1 where [po#] like '%ty%';");
                }
                if(!boName.equals(""))
                {
                    String str = null;
                    st4.execute("delete * from tyg_twbo;");
                    st4.execute("delete * from tyg_twbo1;");
                    st4.execute((new StringBuilder()).append("delete * from tblcontainershippedbo where boname = '").append(boName).append("';").toString());
                    str = (new StringBuilder()).append("select [dnk#] as dnk_no, [b/o] as qty, remarks from ").append(boName).append(";").toString();
                    //System.out.println(str);
                    st4.execute(str);
                    ResultSet rs4 = st4.getResultSet();
                    if(rs4 != null)
                        while(rs4.next()) 
                        {
                            String dnk_no = rs4.getString("dnk_no");
                            long qty = rs4.getLong("qty");
                            String remarks1 = rs4.getString("remarks");
                            if (remarks1 == null)
                                remarks1 = " ";
                            String remarks = remarks1.replace("'", " ");
                            try
                            {
                                st2.execute((new StringBuilder()).append("insert into tblcontainershippedbo values('").append(boName).append("','").append(dnk_no).append("', ").append(qty).append(",'").append(remarks).append("');").toString());
                            }
                            catch(SQLException er) { 
                                er.printStackTrace(); 
                            }
                        }
                    st4.execute((new StringBuilder()).append("INSERT INTO TYG_TWBO ( [DNK#], [B/O], REMARKS ) SELECT DNK_NO, QTY, REMARKS FROM tblcontainershippedbo where boname = '").append(boName).append("';").toString());
                    st4.execute("INSERT INTO TYG_TWBO1 ( [DNK#], [B/O] ) SELECT [DNK#], SUM([B/O]) FROM TYG_TWBO GROUP BY [DNK#];");
                    st4.execute("UPDATE TYG_TWBO1 AS A INNER JOIN TYG_TWBO AS B ON A.[DNK#] = B.[DNK#] SET A.REMARKS = B.REMARKS;");
                    st4.execute("UPDATE TYG_TWBO1 SET REMARKS = ' ' WHERE REMARKS = 'NULL';");
                }
            }
            catch(SQLException er)
            {
                er.printStackTrace();
                JOptionPane.showMessageDialog(null, er);
            }
            st.close();
            st2.close();
            st4.close();
            st5.close();
            con.close();
        }
        catch(Exception e)
        {
            e.printStackTrace();
            JOptionPane.showMessageDialog(null, e);
        }

    }
}
